﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using OrderManager.DataAccess.Extentions;
using OrderManager.Domain.Entities;
using OrderManager.Domain.Repositories;

namespace OrderManager.DataAccess.Repositories
{
    /// <summary>
    /// Provides actions to manipulate [Order] table in database. Implements <see cref="OrderManager.Domain.Repositories.IOrderRepository"/> interface
    /// </summary>
    public class OrderRepository : IOrderRepository
    {
        #region Implementation of IOrderRepository

        /// <summary>
        /// Adds new order to database with specified number, date, amount and description for customer with specified id
        /// </summary>
        /// <param name="number">Orders number</param>
        /// <param name="orderDate">Orders date</param>
        /// <param name="amount">Orders amount</param>
        /// <param name="description">Orders description</param>
        /// <param name="customerId">Id of customer to attach order</param>
        /// <returns><see cref="OrderManager.Domain.Entities.Order"/> with specified parameters</returns>
        public Order Create(int number, DateTime orderDate, int amount, string description, int customerId)
        {
            using (UnitOfWork uow = new UnitOfWork())
            {
                string sql = string.Format("INSERT INTO Sales.[Order]" +
                    "(number, order_date, amount, description, customer_id) VALUES" +
                    "(@number, @order_date, @amount,@description,@customer_id); SELECT SCOPE_IDENTITY();");

                int orderId;
                using (SqlCommand cmd = new SqlCommand(sql, uow.Connection))
                {
                    cmd.AddParameter("@number", number, SqlDbType.Int);
                    cmd.AddParameter("@order_date", orderDate, SqlDbType.Date);
                    cmd.AddParameter("@amount", amount, SqlDbType.Int);
                    cmd.AddParameter("@description", description, SqlDbType.VarChar);
                    cmd.AddParameter("@customer_id", customerId, SqlDbType.Int);

                    orderId = Convert.ToInt32(cmd.ExecuteScalar());
                }
                return new Order(orderId, number, orderDate, amount, description, customerId);
            }
        }

        /// <summary>
        /// Deletes order with specified id from database 
        /// </summary>
        /// <param name="id">Id of order to delete</param>
        public void Delete(Int32 id)
        {
            using (UnitOfWork uow = new UnitOfWork())
            {
                string sql = "DELETE FROM Sales.[Order] WHERE order_id = @order_id";
                using (SqlCommand cmd = new SqlCommand(sql, uow.Connection))
                {
                    cmd.AddParameter("@order_id", id, SqlDbType.Int);
                    cmd.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// Gets a list of all orders in database
        /// </summary>
        /// <returns>Collection of <see cref="OrderManager.Domain.Entities.Order"/></returns>
        public List<Order> GetAll()
        {
            using (UnitOfWork uow = new UnitOfWork())
            {
                List<Order> orders = new List<Order>();
                string sql = "SELECT order_id, number, order_date, amount, description, customer_id FROM Sales.[Order]";
                using (SqlCommand cmd = new SqlCommand(sql, uow.Connection))
                {
                    using (SqlDataReader dr = cmd.ExecuteReader())
                    {
                        while (dr.Read())
                        {
                            var orderId = Convert.ToInt32(dr["order_id"]);
                            var orderNumber = Convert.ToInt32(dr["number"]);
                            var orderDate = Convert.ToDateTime(dr["order_date"]);
                            var orderAmount = Convert.ToInt32(dr["amount"]);
                            var orderDescription = Convert.ToString(dr["description"]);
                            var orderCustomerId = Convert.ToInt32(dr["customer_id"]);
                            orders.Add(new Order(orderId,
                                orderNumber,
                                orderDate,
                                orderAmount,
                                orderDescription,
                                orderCustomerId));
                        }
                    }
                }
                return orders;
            }
        }

        /// <summary>
        /// Gets an order with specified id from database
        /// </summary>
        /// <param name="id">Id of order to get</param>
        /// <returns><see cref="OrderManager.Domain.Entities.Order"/> with specified id (other parameters from database)</returns>
        public Order GetById(int id)
        {
            using (UnitOfWork uow = new UnitOfWork())
            {
                Order order;
                string sql = "SELECT order_id, number, order_date, amount, description, customer_id " +
                    "FROM Sales.[Order] WHERE order_id = @order_id";
                using (SqlCommand cmd = new SqlCommand(sql, uow.Connection))
                {
                    cmd.AddParameter("@order_id", id, SqlDbType.Int);
                    using (SqlDataReader dr = cmd.ExecuteReader())
                    {
                        var orderId = Convert.ToInt32(dr["order_id"]);
                        var orderNumber = Convert.ToInt32(dr["number"]);
                        var orderDate = Convert.ToDateTime(dr["order_date"]);
                        var orderAmount = Convert.ToInt32(dr["amount"]);
                        var orderDescription = Convert.ToString(dr["description"]);
                        var orderCustomerId = Convert.ToInt32(dr["customer_id"]);
                        order = new Order(orderId,
                                orderNumber,
                                orderDate,
                                orderAmount,
                                orderDescription,
                                orderCustomerId);
                    }
                }
                return order;
            }
        }
        
        /// <summary>
        /// Gets a list of all orders for the customer with specified id
        /// </summary>
        /// <param name="customerId">Id of the customer with orders to be got</param>
        /// <returns>Collection of <see cref="OrderManager.Domain.Entities.Order"/></returns>
        public List<Order> GetOrdersForCustomer(int customerId)
        {
            using (UnitOfWork uow = new UnitOfWork())
            {
                List<Order> orders = new List<Order>();
                string sql = "SELECT order_id, number, order_date, amount, description, customer_id " +
                    "FROM Sales.[Order] WHERE customer_id=@customer_id";
                using (SqlCommand cmd = new SqlCommand(sql, uow.Connection))
                {
                    cmd.AddParameter("@customer_id", customerId, SqlDbType.Int);
                    using (SqlDataReader dr = cmd.ExecuteReader())
                    {
                        while (dr.Read())
                        {
                            var orderId = Convert.ToInt32(dr["order_id"]);
                            var orderNumber = Convert.ToInt32(dr["number"]);
                            var orderDate = Convert.ToDateTime(dr["order_date"]);
                            var orderAmount = Convert.ToInt32(dr["amount"]);
                            var orderDescription = Convert.ToString(dr["description"]);
                            var orderCustomerId = Convert.ToInt32(dr["customer_id"]);
                            orders.Add(new Order(orderId,
                                orderNumber,
                                orderDate,
                                orderAmount,
                                orderDescription,
                                orderCustomerId));
                        }
                    }
                }
                return orders;
            }
        }

        /// <summary>
        /// Updates the entity of order in database with values of properties of specified Order
        /// </summary>
        /// <param name="order">Instance of <see cref="OrderManager.Domain.Entities.Order"/> class with new values</param>
        public void Update(Order order)
        {
            using (UnitOfWork uow = new UnitOfWork())
            {
                string sql = "UPDATE Sales.[Order] SET number=@number, order_date=@order_date, amount=@amount, description=@description " +
                    "WHERE customer_id=@customer_id";

                using (SqlCommand cmd = new SqlCommand(sql, uow.Connection))
                {
                    cmd.AddParameter("@number", order.Number, SqlDbType.Int);
                    cmd.AddParameter("@order_date", order.OrderDate, SqlDbType.DateTime);
                    cmd.AddParameter("@amount", order.Amount, SqlDbType.Int);
                    cmd.AddParameter("@description", order.Desctiption, SqlDbType.VarChar);

                    cmd.ExecuteNonQuery();
                }
            }
        }
        #endregion
    }
}